<?

final class sql_counters{

	const delete_condition = "delete from t_counters_conditions where id=%d";
	const delete_counter = "delete from t_counters where id=%d";

	const get_condition = "select * from t_counters_conditions where id=%d;";
	const get_counter = "select * from t_counters where id=%d;";
	
	const get_counters_brief = "
select 
	c.name,sum(cs.session_bytes) as bytes, max(cs.last_update) as last_update 
from 
	t_counters c, t_counters_sessions cs 
where 
	cs.fk_counter=c.id and 
    cs.session_date between '%s' and '%s'
group by
	c.name
order by
	c.id
	";
	
	const get_counter_sum = "
select 
	sum(cs.session_bytes) as bytes, max(cs.last_update) as last_update 
from 
	t_counters c, t_counters_sessions cs
where 
	cs.fk_counter=c.id and c.id=%d and cs.session_date between '%s' and '%s';";

	const get_counters = "select * from t_counters order by id;";
	const get_counters_conditions = "select * from t_counters_conditions where fk_counter=%d;";
	const get_counters_sessions = "
select 
	*
from 
	t_counters_sessions cs
where 
	cs.session_date between '%s' and '%s' %s
order by
	cs.session_date
	";
	
	// To use with known counter_id
	const get_counter_sessions = "
select 
	c.id,c.name,sum(cs.session_bytes) as session_bytes
from 
	t_counters c, t_counters_sessions cs
where 
	cs.fk_counter=c.id and cs.fk_counter=%d and 
	cs.session_date between '%s' and '%s'
group by
	c.name
order by
	cs.session_date
	";

	const get_counters_by_day = "
select 
	distinct cs.session_date
from 
	t_counters_sessions cs
where 
	cs.session_date between '%s' and '%s'
order by
	cs.session_date
	";

	// When select without day detalization	
	const get_counters_by_day2 = "
select 
	c.id,c.name,sum(cs.session_bytes) as session_bytes
from 
	t_counters c, t_counters_sessions cs
where 
	cs.fk_counter=c.id and cs.session_date between '%s' and '%s'
group by
	c.name
order by
	cs.session_date


	";
	const get_counter_by_day = "
select 
	cs.session_bytes,c.id,c.name
from 
	t_counters c, t_counters_sessions cs
where 
	cs.fk_counter=c.id and cs.session_date='%s'
";
	const get_detail_by_counter = "
select 
	cd.flow_dstaddr,sum(cd.bytes) as bytes
from 
	t_counters c, t_counters_sessions cs, t_counters_details cd 
where
	cs.fk_counter=c.id and cd.fk_counters_session=cs.id and 
    c.id=%d and cs.session_date between '%s' and '%s'
group by
	cd.flow_dstaddr
order by
	cd.flow_dstaddr
	";

	const get_last_update = "select max(cs.last_update) from t_counters_sessions cs";
	
	const part_counter_id = "and cs.fk_counter=%d";
	
	const insert_counter = "insert into t_counters values (null, '%s', '%s', '%s', %d)";
	const insert_condition = "insert into t_counters_conditions values (null, %d, %d, %u, %d, %d, %u, %d, %d, %d, %d, %d, %u, %u)";

	const update_condition = "update t_counters_conditions set flow_srcaddr=%u, flow_srcport=%d, flow_dstaddr=%u, flow_dstport=%d, flow_proto=%d, flow_srcaddr_mask=%u, flow_dstaddr_mask=%u where id=%u";
	const update_counter = "update t_counters set name='%s', note='%s', rules_policy='%s', detail_dstaddr=%d where id=%d";
}

?>
